Adding Multiple Tables to the Data Model in Excel

Course: Advanced Excel

Topic: Adding Multiple Tables to the Data Model

Course Link: Advanced Excel Course

Demo Link: Register for a Course Demo

Website: JustAcademy Official Website

1. Introduction

Microsoft Excel provides a powerful feature called the Data Model, which allows users to combine and analyze information from multiple related tables without manually merging all the data into one large worksheet. It is particularly useful when working with sales reports, customer records, product details, financial transactions, employee information, and inventory data.

When multiple tables are added to the Data Model, Excel can use relationships between them to create PivotTables, PivotCharts, and analytical reports. For example, a sales table can be connected to a customer table and a product table, allowing users to analyze sales by customer, product category, region, or date.

The Data Model is especially valuable when datasets contain repeated information. Instead of storing customer names and product descriptions in every transaction row, users can maintain separate tables and connect them through matching key columns.

Microsoft's documentation explains that a workbook can contain one Data Model with multiple tables, and that those tables can be used together in PivotTables and PivotCharts. Read Microsoft's guide to creating a Data Model.

2. Learning Objectives

• Understand the meaning and purpose of the Excel Data Model.
• Identify situations where multiple tables should be used instead of one large table.
• Prepare worksheet data for addition to the Data Model.
• Add tables using Power Pivot and Power Query.
• Import multiple tables from external data sources.
• Understand primary keys, foreign keys, and table relationships.
• Create relationships between related tables.
• Build PivotTables using fields from multiple tables.
• Troubleshoot common Data Model problems.
• Apply Data Model techniques to practical business reporting.

3. What Is the Excel Data Model?

3.1 Definition

The Excel Data Model is an integrated collection of tables and their relationships stored within an Excel workbook. It allows the tables to work together as a connected analytical structure.

Instead of combining every column into one worksheet, users can maintain separate tables for different types of information and establish relationships through matching identifiers.

3.2 Example of a Data Model

Imagine a company that maintains three tables:

The Sales table can connect to the Customers table through CustomerID and to the Products table through ProductID. These relationships allow Excel to analyze sales using customer and product information without repeating all those details in every transaction row.

3.3 Why Use a Data Model?

• Reduced duplication: Store descriptive information in separate tables rather than repeating it in every transaction.
• Flexible analysis: Combine fields from different tables in the same PivotTable.
• Improved organization: Keep customer, product, date, and transaction data logically separated.
• Reusable reporting: Use the same model to create multiple PivotTables and PivotCharts.
• Scalable analysis: Work with data from multiple worksheets, files, databases, or supported external sources.
• Advanced calculations: Use Power Pivot and DAX features where supported.

4. Understanding Tables Before Adding Them to the Data Model

Before adding data, prepare each dataset so that Excel can recognize its structure and identify relationships reliably.

4.1 Convert Data Ranges into Excel Tables

An Excel Table is a structured range containing headers and records. Tables are easier to manage than ordinary cell ranges because they support structured references, automatic expansion, filtering, and clearer table names.

Steps to Create an Excel Table

1. Select a cell inside the dataset.
2. Press Ctrl + T, or select Insert > Table.
3. Confirm the selected data range.
4. Select My table has headers if the first row contains column headings.
5. Click OK.
6. Open the Table Design tab.
7. Enter a meaningful table name, such as tblCustomers, tblProducts, or tblSales.

4.2 Characteristics of Well-Prepared Tables

5. Understanding Primary Keys and Foreign Keys

5.1 What Is a Primary Key?

A primary key is a column that uniquely identifies each record in a table. It should contain unique, nonblank values.

For example, CustomerID can identify every customer in the Customers table. No two customer records should share the same CustomerID.

5.2 What Is a Foreign Key?

A foreign key is a column in one table that refers to the key column in another table. It allows records in different tables to be connected.

For example, CustomerID in the Sales table identifies the customer associated with each transaction. The same CustomerID should exist in the Customers table for the relationship to match that customer.

5.3 Primary Key and Foreign Key Example

CustomerID is unique in the Customers table, but it can appear multiple times in the Sales table because one customer can make multiple purchases.

5.4 Important Relationship Rules

• The lookup or primary-key column should contain unique values.
• The corresponding foreign-key column may contain repeated values.
• Related columns must use compatible data types.
• Key values should follow a consistent format, including spaces, leading zeros, and capitalization where relevant.
• Every foreign-key value intended to match a record should exist in the related lookup table.
• Excel Data Models support one-to-many and one-to-one relationships directly; more complex many-to-many scenarios require additional modeling.

For more details, see Microsoft's guide to relationships between tables in a Data Model.

6. Adding Multiple Tables Using Power Pivot

Power Pivot provides tools for managing the Data Model, viewing tables, creating relationships, and performing advanced data analysis. Its availability and exact interface depend on the Excel edition and platform.

6.1 Enable the Power Pivot Tab if Necessary

1. Open Excel and select File > Options.
2. Select Add-ins.
3. At the bottom, choose COM Add-ins from the Manage list and click Go, if this option is available in your Excel edition.
4. Look for Microsoft Power Pivot for Excel.
5. Select its checkbox and click OK.
6. Confirm that the Power Pivot tab appears on the ribbon.

Note: Power Pivot availability varies by Excel version, license, and operating system. If the option is not listed, check the features included in your Excel edition.

6.2 Add the First Table to the Data Model

1. Open the worksheet containing the first prepared Excel Table.
2. Click any cell inside the table.
3. Open the Power Pivot tab.
4. Choose Add to Data Model.
5. Excel opens the Power Pivot window and adds the selected table to the model.
6. Verify that the table appears in the model.

6.3 Add Additional Tables

1. Return to the Excel workbook.
2. Open the worksheet containing the next prepared table.
3. Click a cell inside that table.
4. Select Power Pivot > Add to Data Model.
5. Repeat the process for every additional table that should be part of the model.
6. Open Power Pivot and verify that all expected tables appear.

Once the tables are included, create relationships between matching key columns. Adding tables to the model does not automatically guarantee that every required relationship has been created.

7. Adding Tables Using Power Query

Power Query is useful when importing, cleaning, combining, and transforming data before it is loaded into Excel or the Data Model.

7.1 Import a Table with Power Query

1. Select a cell inside an Excel Table, or select the relevant data source.
2. Open the Data tab.
3. Choose From Table/Range for a worksheet table, or use the appropriate Get Data option for an external source.
4. Review the data in Power Query Editor.
5. Rename columns, correct data types, and remove unwanted rows if necessary.
6. Choose Home > Close & Load To where available.
7. Choose the option to create a connection and add the data to the Data Model if that option is available in the dialog.
8. Confirm the settings and load the data.

7.2 Import Multiple Tables from an External Source

When a supported source contains several related tables, Excel's Get Data experience can allow multiple tables to be selected during import.

1. Open the Data tab and select Get Data.
2. Choose the relevant source, such as an Excel workbook or supported database.
3. Locate the required tables in the Navigator window.
4. Select the required tables. If available, use the option to select multiple items.
5. Choose Transform Data if the tables need cleaning before loading.
6. Review each table and its column types.
7. Load the selected data into the Data Model using the supported load options.
8. Check that all expected tables appear in the workbook's Data Model.

Depending on the import method, Excel may create a Data Model automatically when multiple tables are imported together. Verify the resulting model and its relationships before building reports.

8. Adding an Existing Table to an Already Created Data Model

Sometimes a workbook already contains a Data Model and a user needs to add a new worksheet table. This is common when a company introduces a new product list, regional lookup table, or monthly transaction file.

8.1 Using Power Pivot

1. Ensure the new dataset is formatted as an Excel Table.
2. Give the table a descriptive name.
3. Click inside the table.
4. Select Power Pivot > Add to Data Model.
5. Open Power Pivot and confirm that the table has been added.
6. Identify the matching key column in the new table and the existing model.
7. Create a relationship if one is required.
8. Test the relationship in a PivotTable.

8.2 Using the PivotTable Dialog

In supported desktop Excel editions, a table or range can also be added to the Data Model while creating a PivotTable.

1. Select the relevant table or range.
2. Choose Insert > PivotTable.
3. In the PivotTable creation dialog, select Add this data to the Data Model if the option is available.
4. Choose where the PivotTable should be placed.
5. Click OK and confirm that the model includes the selected table.

For Microsoft's instructions, see Add worksheet data to a Data Model using a linked table.

9. Creating Relationships Between Multiple Tables

Relationships are essential when fields from different tables need to work together in a PivotTable. A table can be present in the Data Model without having a relationship to another table, but cross-table analysis may not work correctly until the necessary relationship is established.

9.1 Create a Relationship Through the Data Tab

1. Confirm that both tables are in the Data Model.
2. Open the Data tab.
3. Select Relationships.
4. In the Manage Relationships dialog, select New.
5. Choose the table containing the foreign key.
6. Select the foreign-key column.
7. Choose the related lookup table.
8. Select the unique primary-key column in the related table.
9. Click OK and verify that the relationship appears in the list.

9.2 Create a Relationship in Power Pivot Diagram View

1. Open Power Pivot > Manage.
2. Switch to Diagram View.
3. Locate the two tables that should be connected.
4. Identify the matching key columns.
5. Drag the unique key column from the lookup table to the matching foreign-key column in the transaction table, if supported by the interface.
6. Verify the relationship line and its one-to-many direction.
7. Open Manage Relationships to confirm the connected columns.

9.3 Example of a Relationship Structure

This structure is an example of a simple star schema: the Sales table is the central transaction table, and Customers, Products, and Calendar provide descriptive details for analysis.

10. Practical Example: Sales Data Model

Scenario: A retail company wants to analyze total sales by customer city, product category, and month. The information is stored in three separate tables.

10.1 Customers Table

10.2 Products Table

10.3 Sales Table

10.4 Build the Model

1. Convert all three datasets into Excel Tables.
2. Name the tables tblCustomers, tblProducts, and tblSales.
3. Add each table to the Data Model.
4. Create a relationship between tblCustomers[CustomerID] and tblSales[CustomerID].
5. Create a relationship between tblProducts[ProductID] and tblSales[ProductID].
6. Check that CustomerID and ProductID are unique in their respective lookup tables.
7. Create a PivotTable using the Data Model.

10.5 Create a PivotTable Report

To analyze sales by city and product category:

• Drag City from the Customers table into Rows.
• Drag Category from the Products table into Columns.
• Drag SalesAmount from the Sales table into Values.
• Confirm that the calculation is Sum of SalesAmount.
• Format the values as currency or a suitable number format.

The resulting report summarizes sales across cities and product categories using data from three separate tables.

11. Creating a PivotTable from the Data Model

1. Click a blank cell in the workbook.
2. Choose Insert > PivotTable or use the PivotTable option in Power Pivot.
3. Select the workbook's Data Model as the source, where the option is available.
4. Choose a new worksheet or an existing worksheet for the report.
5. Expand the tables in the PivotTable Fields list.
6. Drag fields from the required tables into Rows, Columns, Values, and Filters.
7. Confirm that relationships exist between the tables used in the report.
8. Apply number formats, labels, filters, and sorting as required.

When the model relationships are configured correctly, fields from different tables can be used together in the same PivotTable. See Microsoft's instructions for creating relationships between Excel tables.

12. Data Model Versus a Single Combined Table

13. Common Errors and Troubleshooting

14. Best Practices for Managing Multiple Tables

• Use a separate table for each major business entity, such as Customers, Products, Orders, or Employees.
• Give every table a clear and meaningful name.
• Choose stable key columns that uniquely identify lookup records.
• Check for duplicate and blank keys before building relationships.
• Ensure matching columns use compatible data types and consistent formats.
• Prefer simple one-to-many relationships for common reporting models.
• Avoid creating unnecessary relationships that may make reports ambiguous.
• Use Power Query to clean and standardize source data before loading it.
• Validate totals against the original sources after building a PivotTable.
• Refresh queries, connections, and PivotTables after source data changes.
• Document the purpose of each table and its relationships.
• Keep a backup of important workbooks before making major structural changes.

15. Practice Exercises

Exercise 1: Add Multiple Tables

Create three tables named Customers, Products, and Sales. Add all three to the Data Model and verify that they appear in the model.

Exercise 2: Create Relationships

Connect Customers to Sales through CustomerID and Products to Sales through ProductID. Verify that the lookup keys are unique and that the data types are compatible.

Exercise 3: Build a Sales PivotTable

Create a PivotTable that displays sales by customer city and product category. Use SalesAmount as the value field and confirm that it is summarized by Sum.

Exercise 4: Import Data Using Power Query

Import a customer workbook and a separate product workbook. Clean the column names and data types, then load the tables into the Data Model using the available options.

Exercise 5: Troubleshoot Relationships

Create a duplicate CustomerID in the Customers table and attempt to establish a relationship. Observe the issue, remove or resolve the duplicate, and create the relationship successfully.

Exercise 6: Refresh a Report

Add new sales records to the source table, refresh the relevant connections and PivotTable, and verify that the updated report reflects the new records.

16. Interview Questions and Answers

Q1. What is the Data Model in Excel?

Answer: The Data Model is a collection of related tables stored within a workbook. It allows Excel to analyze data from multiple tables together through relationships and features such as PivotTables and PivotCharts.

Q2. Why should we add multiple tables to the Data Model?

Answer: Multiple tables allow information to be organized by business entity, reduce repeated descriptive data, and support flexible analysis across related datasets.

Q3. What is the difference between a primary key and a foreign key?

Answer: A primary key uniquely identifies records in a lookup table. A foreign key refers to that key from another table and is used to connect related records.

Q4. How can you add a worksheet table to the Data Model?

Answer: In a supported Excel edition, select a cell in the table and use Power Pivot > Add to Data Model. Alternatively, use a supported PivotTable or Power Query loading workflow that includes the Data Model.

Q5. What is the role of Power Query?

Answer: Power Query imports, cleans, transforms, and combines data from various sources. Its output can be loaded into Excel and, where supported, into the Data Model.

Q6. What is a one-to-many relationship?

Answer: It connects one unique record in a lookup table to multiple records in another table. For example, one customer can be associated with many sales transactions.

Q7. Can two tables be in the Data Model without a relationship?

Answer: Yes. However, fields from unrelated tables generally cannot be used together meaningfully in a PivotTable until the necessary relationship or appropriate modeling structure is created.

Q8. Why must the lookup column contain unique values?

Answer: Excel needs an unambiguous lookup record on the one side of a relationship. Duplicate values in that column can prevent the relationship from being created correctly.

Q9. How can you view relationships between tables?

Answer: In supported desktop editions, use Power Pivot's Diagram View or the Manage Relationships dialog. The Data tab may also provide a Relationships command.

Q10. How do you update a Data Model after the source data changes?

Answer: Update the source data and refresh the relevant query or connection and any dependent PivotTables. The precise refresh behavior depends on how the table was added and linked to the model.

17. Quick Revision Checklist

• The Data Model allows multiple related tables to work together in Excel.
• Prepare each dataset with clear headers and consistent data types.
• Convert worksheet ranges into Excel Tables where appropriate.
• Use meaningful table names such as tblCustomers and tblSales.
• Add tables through Power Pivot, Power Query, or another supported loading method.
• Use primary and foreign keys to establish relationships.
• Ensure lookup keys are unique and matching columns have compatible data types.
• Use fields from related tables to create PivotTables and PivotCharts.
• Check relationships and totals if a report looks incorrect.
• Refresh data connections and reports after source data changes.

18. Conclusion

Adding multiple tables to the Excel Data Model is an important advanced Excel skill for creating flexible and reliable analytical reports. Rather than combining every field into a single large worksheet, users can keep customers, products, transactions, and other information in separate tables connected through key columns.

By preparing tables correctly, adding them to the model, creating valid relationships, and building PivotTables from related fields, analysts can produce reports that are easier to maintain and more useful for business decisions. Power Query and Power Pivot further improve this workflow by supporting repeatable data preparation and advanced analysis.

19. Additional Learning Resources

Microsoft Support: Create a Data Model in Excel

Microsoft Support: Create a Relationship Between Tables

Microsoft Support: Add Worksheet Data to a Data Model

20. Explore Advanced Excel Training

Build your practical Excel skills in data analysis, Power Query, PivotTables, dashboards, reporting, and Data Model management.

Explore the Advanced Excel Course

Register for a Course Demo

Visit JustAcademy Official Website